﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using log4net;
using SISCON.Common;
using SISCON.Model;

namespace SISCON.Dao
{

    /// <summary>
    /// Clase que realiza todas las actualizaciones en la base de datos de los cursos
    /// </summary>
    public class UploadDAO : CommonDAO
    {
        public void DeleteUploadedData(long userId)
        {
            SqlConnection conn = null;
            SqlTransaction trx = null;
            SqlCommand sqlCommand = null;
            string sql = null;

            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();
                trx = conn.BeginTransaction();

                sql = "DELETE FROM sis_course_schedule WHERE course_id IN (SELECT course_id FROM sis_course WHERE created_by = @userId)";

                sqlCommand = new SqlCommand(sql, conn, trx);
                sqlCommand.Parameters.Add(getNewParameter("@userId", SqlDbType.BigInt, 0, userId));
                sqlCommand.Prepare();

                sqlCommand.ExecuteNonQuery();

                sql = "DELETE FROM sis_course_teacher WHERE course_id IN (SELECT course_id FROM sis_course WHERE created_by = @userId)";

                sqlCommand = new SqlCommand(sql, conn, trx);
                sqlCommand.Parameters.Add(getNewParameter("@userId", SqlDbType.BigInt, 0, userId));
                sqlCommand.Prepare();

                sqlCommand.ExecuteNonQuery();

                sql = "DELETE FROM sis_teacher WHERE created_by = @userId";

                sqlCommand = new SqlCommand(sql, conn, trx);
                sqlCommand.Parameters.Add(getNewParameter("@userId", SqlDbType.BigInt, 0, userId));
                sqlCommand.Prepare();

                sqlCommand.ExecuteNonQuery();

                sql = "DELETE FROM sis_classroom WHERE created_by = @userId";

                sqlCommand = new SqlCommand(sql, conn, trx);
                sqlCommand.Parameters.Add(getNewParameter("@userId", SqlDbType.BigInt, 0, userId));
                sqlCommand.Prepare();

                sqlCommand.ExecuteNonQuery();

                sql = "DELETE FROM sis_course WHERE created_by = @userId";
                
                sqlCommand = new SqlCommand(sql, conn, trx);
                sqlCommand.Parameters.Add(getNewParameter("@userId", SqlDbType.BigInt, 0, userId));
                sqlCommand.Prepare();

                sqlCommand.ExecuteNonQuery();

                sql = "DELETE FROM sis_schedule WHERE created_by = @userId";

                sqlCommand = new SqlCommand(sql, conn, trx);
                sqlCommand.Parameters.Add(getNewParameter("@userId", SqlDbType.BigInt, 0, userId));
                sqlCommand.Prepare();

                sqlCommand.ExecuteNonQuery();

                trx.Commit();
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }
    }
}